import {Dictionary, DateFormat} from '@/api/config'
import {downFile, fileHeadCheck, shallowCopy, getYMDHMSStr, getYearMonthDayStr} from '@/util/common'
import Execl from 'exceljs'
import {Worksheet, ValueType} from 'exceljs'
//读取并解析xlsx数据方法的返回数据格式
export interface ReadXLSXRes{
  allData:Array<any>  //返回生成的所有行数据
  rightData:Array<any> //返回生成正确的行数据
  hasErrorData:boolean //是否有错误的数据
  errorCells:Array<any> //错误数据的单元格
  errorRowNumbers:Array<number> //错误数据所在的行数
  errorCellkeys:Array<string> //错误数据所在的单元格
  tipBook:Execl.Workbook|null //生成的提示表格
  unSetColProp:Dictionary<string>|undefined //没有写的表格列
}

//清除工作簿的数据
export const clearWorkBook = (workBook:Execl.Workbook) => {
  workBook.eachSheet((sheet) => {
    sheet.destroy()
  })
}

/**
 * @description: 转换单元格数据
 * @param {Execl} cell // 单元格数据
 * @param {DateFormat} dateType // 日期格式
 * @param {boolean} richText // 是否返回富文本
 * @return {*}
 */
export const transCellValue = (cell:Execl.Cell, dateType?:DateFormat, richText = true):string|number|boolean|undefined => {
  //基础类型直接返回值
  if (cell.type === ValueType.Boolean || cell.type === ValueType.Number || cell.type === ValueType.String) {
    return cell.value as string|number|boolean
  }
  else if (cell.type === ValueType.Date) {
    const date = cell.value as Date
    if (!dateType || dateType === 'YYYY-MM-DD HH:mm:ss') {
      return getYMDHMSStr(date.getTime())
    } else {
      return getYearMonthDayStr(date.getTime())
    }
  }
  // 富文本类型
  else if (cell.type === ValueType.RichText) {
    return richText ? cell.value as string|number|boolean : cell.text
  } else {
    return undefined
  }
}

/**
 * 读取并解析xlsx数据
 * @param inBuffer 数据源 ArrayBuffer
 * @param props key映射 如：{'性别':"sex",'姓名':'name','年龄':"age"} 接口所需要的数据为{sex:xx,name:xxx,age:xx},...
 * @param propsCheckCall key对应的数据筛选 如：{'sex':()=>{},'name':()=>{}}
 * @param generateTipBook 是否生成提示表格
 * @param richText 是否返回富文本格式数据
 * @param startReadLine 开始读取行数，默认为 1
 * @returns
 */
export const readXLSX = (inBuffer:ArrayBuffer, dateType?:DateFormat, props?:{[key:string]:string}, propsCheckCall?:{[key:string]:any}, generateTipBook?:boolean, richText?:boolean, startReadLine = 1):Promise<ReadXLSXRes> => {
  return new Promise<ReadXLSXRes>((resolve, reject) => {
    const workBook = new Execl.Workbook()
    const allData:Array<any> = []
    const rightData:Array<any> = []
    const errorCells:Array<any> = []
    const errorRowNumbers:Array<number> = []
    const errorCellkeys:Array<string> = []
    let tipBook:Execl.Workbook|null = null
    const res:ReadXLSXRes = {allData:allData, rightData:rightData, hasErrorData:false, errorCells:errorCells, errorRowNumbers:errorRowNumbers, errorCellkeys:errorCellkeys, tipBook:tipBook, unSetColProp:undefined}
    let copyProps:undefined| {[key:string]:string} = undefined
    if (props) copyProps = shallowCopy(props)
    workBook.xlsx.load(inBuffer).then(() => {
      if (generateTipBook) {
        tipBook = new Execl.Workbook()
        res.tipBook = tipBook as any
      }
      //先遍历工作簿
      workBook.eachSheet((worksheet) => {
        //跳过隐藏的表格
        if (worksheet.state === 'hidden') {
          return
        }
        const setProps:Array<string> = []
        let tipSheet:Worksheet|null = null
        if (tipBook) {
          tipSheet = tipBook.addWorksheet(worksheet.name)
        }
        //再逐行遍历表格
        worksheet.eachRow((row, rowNumber) => {
          // 如果小于开始行不读取行
          if (rowNumber < startReadLine) {
            return
          }
          const values:Array<any> = []
          const lineData:{[key:string]:any} = {}
          const lineErrorNumbers:Array<number> = []
          let lineFlag = false
          //最后遍历每个单元格
          row.eachCell({includeEmpty:true}, (cell, cellNumber) => {
            const cellValue = cell.value as string|null
            //第一行 提取key
            if (rowNumber === startReadLine) {
              if (cellValue !== null) {
                //有指定对应关系
                if (copyProps) {
                  const prop:string|undefined = copyProps[cellValue]
                  setProps.push(prop)
                  //删除key 以确定少写了哪列 TODO
                  delete copyProps[cellValue]
                }
                //没有指定对应关系
                else {
                  setProps.push(cellValue)
                }
              }
            }
            else {
              const key:string|undefined = setProps[cellNumber - 1]
              if (key !== undefined) {
                lineData[key] = transCellValue(cell, dateType, richText)
              }
              //先看是否有数据筛选
              if (propsCheckCall && propsCheckCall[key]) {
                const callFun = propsCheckCall[key]
                // 这里用了transCellValue转换后的数据
                const newValue:string|undefined = callFun(lineData[key])
                //返回undefined为匹配失败 不返回true或false 因为值可能也为true或false
                if (newValue === undefined) {
                  if (!lineFlag) errorRowNumbers.push(rowNumber)
                  lineFlag = true
                  res.hasErrorData = true
                  errorCells.push({key:cell.address, value:cellValue})
                  errorCellkeys.push(cell.address)
                  lineErrorNumbers.push(cellNumber)
                } else {
                  if (key !== undefined) {
                    lineData[key] = newValue
                  }
                }
              }
            }
            values.push(cellValue)
          })
          if (rowNumber === startReadLine) {
            //
            console.log('没有写的表格列', copyProps)
            console.log('表头数据index映射', setProps)
          }
          else {
            //再检查一次数据 因为暂时不知道为什么有的空单元格可以遍历到,有的不可以。
            for (let i = 0, l = setProps.length; i < l; i++) {
              if (setProps[i] !== undefined && lineData[setProps[i]] === undefined) lineData[setProps[i]] = null
            }
            //添加数据
            allData.push(lineData)
            if (!lineFlag) rightData.push(lineData)
          }
          if (tipSheet) {
            if (rowNumber === startReadLine) {
              for (const key in copyProps) {
                values.push(key)
              }
            }
            tipSheet.addRow(values)
            //有错误需要改颜色
            const len = lineErrorNumbers.length
            if (lineFlag && len && tipSheet.lastRow) {
              let index = 0
              tipSheet.lastRow.eachCell({includeEmpty:true}, (cell, cellNumber) => {
                if (index < len && cellNumber === lineErrorNumbers[index]) {
                  index++
                  cell.fill = {type:'pattern', pattern:'solid', fgColor:{argb:'FF' + 'FFFF00'}}
                }
              })
            }
            //列的颜色处理
            if (rowNumber === startReadLine && tipSheet && tipSheet.lastRow) {
              tipSheet.lastRow.eachCell({includeEmpty:true}, (cell, cellNumber) => {
                //对于添加的列 加上颜色
                if (copyProps && copyProps[cell.value as string]) {
                  cell.fill = {type:'pattern', pattern:'solid', fgColor:{argb:'FF' + 'FFFF00'}}
                }
              })
            }
          }
        })
      })
      clearWorkBook(workBook)
      res.unSetColProp = copyProps
      resolve(res)
    }).catch((error) => {
      clearWorkBook(workBook)
      reject(error)
    })
  })
}

//读取工作簿行数据,转换成对象
export const readWorksheetRow = (row:Execl.Row, headProps:Array<string>, dateType?:DateFormat, transProps?:Dictionary<string>):Dictionary<any> => {
  const data:Dictionary<any> = {}
  row.eachCell({includeEmpty:true}, (cell, cellNumber) => {
    let prop = headProps[cellNumber]
    const transProp = transProps ? transProps[prop] : undefined
    if (transProp) prop = transProp
    //边界情况
    data[prop] = transCellValue(cell, dateType)
  })
  return data
}
//读取工作簿行数据,转换成数组
export const readWorksheetRowArr = (row:Execl.Row, dateType?:DateFormat):Array<any> => {
  const data:Array<any> = []
  row.eachCell({includeEmpty:true}, (cell) => {
    data.push(transCellValue(cell, dateType))
  })
  return data
}


/**
 * @description: 读取工作簿,转换成数组数据
* @param startReadLine 开始读取行数，默认为 1
 * @return {*}
 */
export const readWorksheet = <T>(worksheet:Worksheet, dateType?:DateFormat, props?:Dictionary<string>, startReadLine = 1):Array<T> => {
  const arr:Array<any> = []
  if (worksheet.state) {
    return []
  } else {
    let headProps:Array<string> = []
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber === startReadLine) {
        headProps = readWorksheetRowArr(row, dateType)
      } else {
        arr.push(readWorksheetRow(row, headProps, dateType, props))
      }
    })
  }
  return arr
}

//读取表格,转换成JSON对象
export const readXLSXTOJson = <T>(inBuffer:ArrayBuffer, dateType?:DateFormat, props?:Dictionary<string>):Promise<Dictionary<Array<T>>> => {
  return new Promise<Dictionary<Array<T>>>((resolve, reject) => {
    const workBook = new Execl.Workbook()
    workBook.xlsx.load(inBuffer).then(() => {
      const jsonData:Dictionary<Array<T>> = {}
      workBook.eachSheet((worksheet) => {
        jsonData[worksheet.name] = readWorksheet(worksheet, dateType, props)
      })
      clearWorkBook(workBook)
    }).catch((error) => {
      clearWorkBook(workBook)
      reject(error)
    })
  })
}

//读取某一列
export const readXLSXCol = (inBuffer:ArrayBuffer, prop:string):Promise<any> => {
  return new Promise<any>((resolve, reject) => {
    const workBook = new Execl.Workbook()
    workBook.xlsx.load(inBuffer).then(() => {
      const map:any = {}
      workBook.eachSheet((workSheet) => {
        if (workSheet.state === 'hidden') {
          return
        }
        //先从第一行找到prop对应的列数
        const fristrow = workSheet.getRow(1)
        let propIndex = -1
        fristrow.eachCell((cell, colNumber) => {
          if (cell.value === prop) {
            propIndex = colNumber
          }
        })
        if (propIndex === -1) return
        const col = workSheet.getColumn(propIndex)
        if (!col) return
        col.eachCell((cell) => {
          if (cell.value) map[cell.value + ''] = null
        })
      })
      resolve(map)
    }).catch(() => {
      reject()
    })
  })
}

//检查XLSX文件头
export const checkXLSX = (file:File):Promise<null> => {
  return fileHeadCheck(file, 'xlsx')
}

//下载XLSX文件
export const downXLSXByBook = (workBook:Execl.Workbook, name:string):Promise<null> => {
  return new Promise<null>((resolve, reject) => {
    workBook.xlsx.writeBuffer().then((outBuffer) => {
      downFile(name, outBuffer)
      resolve(null)
    }).catch(() => {
      reject()
    })
  })
}

export interface ExportSheetData{
  data:Array<Array<number|string|null>> //工作簿数据 如果对应字段没用数据，请用null 不然不能一一对应
  head:Array<string> //工作簿表头
  colWidth?:Array<number> //工作簿列宽
  sheetName:string //工作簿名字
}
//直接导出数据
export const exportDataToXLSX = (sheetsData:Array<ExportSheetData>, bookName:string):Promise<null> => {
  return new Promise<null>((resolve, reject) => {
    const workBook = new Execl.Workbook()
    for (let i = 0, l = sheetsData.length; i < l; i++) {
      const sheetData = sheetsData[i]
      const sheet = workBook.addWorksheet(sheetData.sheetName)
      //设置列宽 TODO
      sheet.addRow(sheetData.head)
      const {data} = sheetData
      for (let k = 0, kl = data.length; k < kl; k++) {
        sheet.addRow(data[k])
      }
    }
    downXLSXByBook(workBook, bookName).then(() => {
      clearWorkBook(workBook)
      resolve(null)
    }).catch(() => {
      clearWorkBook(workBook)
      reject()
    })
  })
}